-- **************************************************************************
--    Project Name:   中心错分汇总
--    Job Name:       jms_dm.dm_tab_loopbag_buildbag_summary_dt
--    Description :   中心错分汇总
--    Author :        申家明
--    date：          2022/08/05
-- **************************************************************************
--    Relation ：
--    jms_dm.dm_tab_loopbag_buildbag_summary_dt << [
--       jms_dim.dim_network_whole_massage,
--       jms_dm.dm_tab_missrate_waybill_detail_dt,
--       jms_dm.dm_tab_missrate_trace_waybill_next_dt
--    ]
-- **************************************************************************
 with  network_whole as (
         select id
              , code
              , name
              , agent_code
              , agent_name
              , network_type --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
         from jms_dim.dim_network_whole_massage
     ),
     result_t as (
         select date_time                  as scan_time
              , send_station_network_code  as site_code
              , count(distinct waybill_no) as operate_qty
              , 0                          as errorseparate_qty
              , 0                          as sdmmiss_qty
              , 0                          as sdmerror_qty
              , 0                          as left_qty
         from (
                  select *
                  from jms_dm.dm_tab_missrate_trace_waybill_next_dt
                  where dt = '{{ execution_date | cst_ds }}'
              ) waybill_next
         group by date_time
                , send_station_network_code
         union all

         select day_time                                                as scan_time
              , site_code
              , 0                                                       as operate_qty
              , count(distinct billcode)                                as errorseparate_qty
              , sum(case when errorseparate_type = 1 then 1 else 0 end) as sdmmiss_qty
              , sum(case when errorseparate_type = 2 then 1 else 0 end) as sdmerror_qty
              , sum(case when errorseparate_type = 3 then 1 else 0 end) as left_qty
         from jms_dm.dm_tab_missrate_waybill_detail_dt
         where dt in (date_add('{{ execution_date | cst_ds }}', -4), '{{ execution_date | cst_ds }}')
         group by day_time, site_code
     )
insert
overwrite
table
jms_dm.dm_tab_missrate_sitecode_summary_dt
partition
(
dt
)
select scan_time
     , site_code
     , name                                                         as site_name
     , agent_code                                                   as proxy_area_code
     , agent_name                                                   as proxy_area_name
     , operate_qty
     , errorseparate_qty
     , sdmmiss_qty
     , sdmerror_qty
     , left_qty
     , case
           when operate_qty = 0 then 0
           else round(left_qty / operate_qty * 100, 2) end          as operate_cuofen_rate
     , case
           when operate_qty = 0 then 0
           else round(errorseparate_qty / operate_qty * 100, 2) end as cuofen_rate
     , scan_time                                                    as dt
from (
         select scan_time
              , site_code
              , sum(operate_qty)       as operate_qty
              , sum(errorseparate_qty) as errorseparate_qty
              , sum(sdmmiss_qty)       as sdmmiss_qty
              , sum(sdmerror_qty)      as sdmerror_qty
              , sum(left_qty)          as left_qty
         from result_t a
         group by scan_time
                , site_code
     ) a
         left join network_whole b
                   on a.site_code = b.code
 distribute by 1
;